package com.pucong.gitee.database.backup.database.util;

import com.pucong.gitee.database.backup.common.entity.BackupDatabase;
import com.pucong.gitee.database.backup.common.entity.BackupExeSqlLog;
import com.pucong.gitee.database.backup.common.entity.DatabaseTable;
import com.pucong.gitee.database.backup.common.entity.DatabaseTableFiled;
import com.pucong.gitee.database.backup.common.selfenum.CommonBoolenEnum;
import com.pucong.gitee.database.backup.configuration.exception.BusinessException;
import com.pucong.gitee.database.backup.database.mapper.BackupExeSqlLogMapper;
import com.pucong.gitee.database.backup.database.vo.ExeSqlResult;
import com.pucong.gitee.database.backup.utils.UUID;
import com.pucong.gitee.database.backup.utils.jdbcPool.JdbcConnectionManager;
import org.apache.ibatis.jdbc.ScriptRunner;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import java.io.FileInputStream;
import java.io.InputStreamReader;
import java.math.BigDecimal;
import java.sql.*;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

public abstract class AbstractBackupService implements BackupService {

    private static Logger log = LoggerFactory.getLogger(AbstractBackupService.class);

    protected static final String URL_PREFIX = "jdbc:postgresql://";

    protected static final String URL_LAST = "useUnicode=true&characterEncoding=UTF-8&allowMultiQueries=true";

    private CallableStatement callableStatement = null;
    private Connection conn = null;
    private PreparedStatement pst = null;
    private ResultSet rst = null;
    protected BackupDatabase backupDatabase = null;

    protected String driverClassName = null;
    protected String url = null;
    protected String username = null;
    protected String password = null;
    private String sessionId = null;
    private BackupExeSqlLogMapper backupExeSqlLogMapper = null;

    @Override
    public String getSessionId() {
        return sessionId;
    }

    @Override
    public void setSessionId(String sessionId) {
        this.sessionId = sessionId;
    }

    /**
     * 获取数据库名列表
     * @return
     */
    public String getGET_BASE_LIST_SQL() {
        return "select datname dataBaseName " +
                " from pg_database t1 " +
                "where 1=1 " +
                "and datname not in ('template0','template1')";
    }

    /**
     * 获取数据库下模式名列表
     * @return
     */
    public String getGET_BASE_SCHEMA_LIST_SQL() {
        return "SELECT schema_name schemaName FROM information_schema.schemata where schema_name not in ('information_schema','pg_catalog','pg_toast_temp_1','pg_temp_1','pg_toast') and catalog_name=? ";
    }

    /**
     * 获取表字段列表
     * @return
     */
    public String getGET_TABLE_LIST_SQL() {
        return "select  a.relname AS tableName, a.reltuples as rowCounts,c.description AS tableComment " +
                "from pg_class a " +
                "left join pg_namespace b on a.relnamespace=b.oid " +
                "left join pg_description c on c.objoid =a.oid and c.objsubid=0 " +
                "where 1=1   " +
                " and b.nspname=? " +
                " and a.relname =?";
    }

    /**
     * 获取表字段列表
     * @return
     */
    public String getGET_TABLE_FILED_LIST_SQL() {
        return "SELECT a.attnum, " +
                "       a.attname AS filedName, " +
                "       t.typname AS filedType, " +
                "       a.attlen AS filedSize, " +
                "       a.atttypmod AS lengthvar, " +
                "       a.attnotnull AS notNull, " +
                "       b.description AS filedComment " +
                "  FROM pg_class c, " +
                "       pg_attribute a " +
                "       LEFT OUTER JOIN pg_description b ON a.attrelid=b.objoid AND a.attnum = b.objsubid, " +
                "       pg_type t,pg_namespace d  " +
                " WHERE 1=1 " +
                " and d.nspname=? " +
                " and  c.relname = ? " +
                " and c.relkind in ('r', 'v', 'm') " +
                " and d.nspname not in ('pg_catalog', 'pg_toast', 'information_schema') " +
                " and a.attnum > 0 " +
                " and c.relnamespace=d.oid " +
                " and a.attrelid = c.oid " +
                " and a.atttypid = t.oid " +
                " ORDER BY a.attnum ";
    }

    public AbstractBackupService(BackupDatabase backupDatabase, BackupExeSqlLogMapper backupExeSqlLogMapper) {
        this.backupDatabase = backupDatabase;
        this.username = backupDatabase.getUsername();
        this.password = backupDatabase.getPassword();
        this.backupExeSqlLogMapper = backupExeSqlLogMapper;
        initUrl();
    }


    @Override
    public void initUrl() {
        this.url = URL_PREFIX  + backupDatabase.getHost() + ":" + backupDatabase.getPort() + "/" + backupDatabase.getDatabaseName() + "?currentSchema="+ backupDatabase.getSchemaName() +"&" + URL_LAST;
    }

    @Override
    public void setDataBase(String databaseName) {
        backupDatabase.setDatabaseName(databaseName);
        initUrl();
    }

    @Override
    public void setSchema(String schema) {
        backupDatabase.setSchemaName(schema);
        initUrl();
    }

    @Override
    public ExeSqlResult getDataBaseList() {
        Object[] params = null;
        ExeSqlResult exeSqlResult = this.executeQuery(getGET_BASE_LIST_SQL(), params);
        return exeSqlResult;
    }

    @Override
    public List<String> getSchemaList(String databaseName) {
        List<String> resultStrList = new ArrayList<>();
        Object[] params = new Object[1];
        params[0] = databaseName;
        ExeSqlResult exeSqlResult = this.executeQuery(getGET_BASE_SCHEMA_LIST_SQL(), params);
        List<Map<String, Object>> resultList = exeSqlResult.getResultList();
        for (Map<String, Object> stringObjectMap : resultList) {
            resultStrList.add(stringObjectMap.get("schemaname").toString());
        }
        return resultStrList;
    }

    @Override
    public List<DatabaseTable> getTableListByDBInfo(String schemaName) {
        List<DatabaseTable> resultTableList = new ArrayList<>();
        Object[] params = new Object[1];
        params[0] = schemaName;
        ExeSqlResult exeSqlResult = this.executeQuery(getGET_TABLE_LIST_SQL(), params);
        List<Map<String, Object>> resultList = exeSqlResult.getResultList();
        for (Map<String, Object> stringObjectMap : resultList) {
            DatabaseTable databaseTable = new DatabaseTable();
            String tableName = (String) stringObjectMap.get("tableName");
            String count = stringObjectMap.get("rowCounts") + "";
            String tableComment = (String) stringObjectMap.get("tableComment");
            databaseTable.setDataSize(new BigDecimal(count));
            databaseTable.setTableName(tableName);
            databaseTable.setTableComment(tableComment);
            resultTableList.add(databaseTable);
        }
        return resultTableList;
    }

    @Override
    public List<DatabaseTableFiled> getTableFileListByDBInfo(String schemaName,String tableName) {
        List<DatabaseTableFiled> resultTableList = new ArrayList<>();
        Object[] params = new Object[2];
        params[0] = schemaName;
        params[1] = tableName;
        ExeSqlResult exeSqlResult = this.executeQuery(getGET_TABLE_FILED_LIST_SQL(), params);
        List<Map<String, Object>> resultList = exeSqlResult.getResultList();
        for (Map<String, Object> stringObjectMap : resultList) {
            DatabaseTableFiled databaseTableFiled = new DatabaseTableFiled();
            String filedName = (String) stringObjectMap.get("filedName");
            String filedSize = (String) stringObjectMap.get("filedSize");
            String filedType = (String) stringObjectMap.get("filedType");
            String primaryKey = (String) stringObjectMap.get("primaryKey");
            String notNull = (String) stringObjectMap.get("notNull");
            String defaultValue = (String) stringObjectMap.get("defaultValue");
            String filedComment = (String) stringObjectMap.get("filedComment");
            databaseTableFiled.setFiledName(filedName);
            databaseTableFiled.setFiledSize(filedSize);
            databaseTableFiled.setFiledType(filedType);
            databaseTableFiled.setPrimaryKey("1".equals(primaryKey) ? CommonBoolenEnum.是:CommonBoolenEnum.否);
            databaseTableFiled.setNotNull("1".equals(notNull) ? CommonBoolenEnum.是:CommonBoolenEnum.否);
            databaseTableFiled.setFiledComment(filedComment);
            databaseTableFiled.setDefaultValue(defaultValue);
            resultTableList.add(databaseTableFiled);
        }
        return resultTableList;
    }

    @Override
    public ExeSqlResult getTableDataListByDBInfo(String tableName, int pageSize, int pageNo) {
        String sql = "select * from " + tableName;
        String sqlPage = this.generatePageSql(sql, pageNo, pageSize);
        BigDecimal count = this.getCountByQuerySql(sql);
        Object[] params = null;
        ExeSqlResult exeSqlResult = this.executeQuery(sqlPage, params);
        exeSqlResult.setResultInteger(count);
        return exeSqlResult;
    }

    @Override
    public BigDecimal getCountByQuerySql(String sql) {
        String sqlCount = "select count(0) count from (" + sql + ")";
        Object[] params = null;
        ExeSqlResult exeSqlResult = this.executeQuery(sqlCount, params);
        List<Map<String, Object>> resultList = exeSqlResult.getResultList();
        String count = (String) resultList.get(0).get("count");
        return new BigDecimal(count);
    }

    @Override
    public Boolean runSqlByScriptRunner(String sqlPath)  {
        try {
            ScriptRunner runner = new ScriptRunner(conn);
            runner.setEscapeProcessing(false);
            runner.setSendFullScript(true);
            runner.runScript(new InputStreamReader(new FileInputStream(sqlPath), "UTF-8"));
        } catch (Exception e) {
            log.error("执行sql文件异常",e);
            throw new BusinessException("执行sql文件异常");
        }

        return true;
    }

    /**
     * 获取连接
     */
    @Override
    public Boolean getConnection() {
        // 获取连接
        log.info("连接数据库",sessionId, url, username, password);
        conn = JdbcConnectionManager.getConnection(sessionId,url, username, password,driverClassName);
        if(conn == null){
            return false;
        }
        return true;
    }

    /**
     * 关闭所有资源
     */
    @Override
    public void closeAll() {
        // 关闭结果集对象
        if (rst != null) {
            try {
                rst.close();
            } catch (SQLException e) {
                log.error("关闭数据库异常",e);
            }
        }

        // 关闭PreparedStatement对象
        if (pst != null) {
            try {
                pst.close();
            } catch (SQLException e) {
                log.error("关闭数据库异常",e);
            }
        }

        // 关闭CallableStatement 对象
        if (callableStatement != null) {
            try {
                callableStatement.close();
            } catch (SQLException e) {
                log.error("关闭数据库异常",e);
            }
        }

        // 关闭Connection 对象
        if (conn != null) {
            try {
                conn.close();
            } catch (SQLException e) {
                log.error("关闭数据库异常",e);
            }
        }
    }

    /**
     * insert update delete SQL语句的执行的统一方法
     * @param sql SQL语句
     * @param params 参数数组，若没有参数则为null
     * @return 受影响的行数
     */
    @Override
    public int executeUpdate(String sql, Object[] params) {
        // 受影响的行数
        int affectedLine = 0;
        try {
            log.info("执行sql: " + sql);
            StringBuffer sb = new StringBuffer();
            if(params != null){
                for(int i = 0; i < params.length; i++){
                    if(params[i] != null){
                        sb. append(params[i].toString().trim()+",");
                    }
                }
            }
            String newStr = sb.toString();
            log.info("执行sql 参数: " + newStr);
            // 插入执行sql
            BackupExeSqlLog insertLog = new BackupExeSqlLog();
            insertLog.setId(UUID.uuid());
            insertLog.setExeSql(sql);
            insertLog.setExeParams(newStr);
            insertLog.setDatabaseId(backupDatabase.getId());
            insertLog.setDatabaseName(backupDatabase.getDatabaseName());
            insertLog.setSchemaName(backupDatabase.getSchemaName());
            insertLog.setExeTime(new Date());
            // 调用SQL
            pst = conn.prepareStatement(sql);

            // 参数赋值
            if (params != null) {
                for (int i = 0; i < params.length; i++) {
                    pst.setObject(i + 1, params[i]);
                }
            }
            /**
             * 在此 PreparedStatement 对象中执行 SQL 语句，
             *  该语句必须是一个 SQL 数据操作语言（Data Manipulation Language，DML）语句，比如 INSERT、UPDATE 或 DELETE
             *  语句；或者是无返回内容的 SQL 语句，比如 DDL 语句。
             */
            affectedLine = pst.executeUpdate();

        } catch (SQLException e) {
            log.error("执行sql异常",e);
        }
        return affectedLine;
    }

    @Override
    public ExeSqlResult executeQuery(String sql, Object[] params) {
        // 执行SQL获得结果集
        try {
            log.info("执行sql: " + sql);
            StringBuffer sb = new StringBuffer();
            if(params != null){
                for(int i = 0; i < params.length; i++){
                    if(params[i] != null){
                        sb. append(params[i].toString().trim()+",");
                    }
                }
            }
            String newStr = sb.toString();
            log.info("执行sql 参数: " + newStr);
            // 插入执行sql
            BackupExeSqlLog insertLog = new BackupExeSqlLog();
            insertLog.setId(UUID.uuid());
            insertLog.setExeSql(sql);
            insertLog.setExeParams(newStr);
            insertLog.setDatabaseId(backupDatabase.getId());
            insertLog.setDatabaseName(backupDatabase.getDatabaseName());
            insertLog.setSchemaName(backupDatabase.getSchemaName());
            insertLog.setExeTime(new Date());
            backupExeSqlLogMapper.insert(insertLog);
            // 调用SQL
            pst = conn.prepareStatement(sql);

            // 参数赋值
            if (params != null) {
                for (int i = 0; i < params.length; i++) {
                    pst.setObject(i + 1, params[i]);
                }
            }

            // 执行
            rst = pst.executeQuery();

        } catch (SQLException e) {
            log.error("执行sql异常",e);
            throw new BusinessException("执行sql异常");
        }

        // 创建ResultSetMetaData对象
        ResultSetMetaData rsmd = null;

        // 结果集列数
        int columnCount = 0;
        try {
            rsmd = rst.getMetaData();

            // 获得结果集列数
            columnCount = rsmd.getColumnCount();
        } catch (SQLException e) {
            log.error("执行sql异常",e);
            throw new BusinessException("执行sql异常");
        }

        ExeSqlResult exeSqlResult = new ExeSqlResult();
        // 创建List
        List<Map<String, Object>> list = new ArrayList<>();
        List<String> filedList = new ArrayList<>();
        try {
            for (int i = 1; i <= columnCount; i++) {
                filedList.add(rsmd.getColumnLabel(i));
            }
            // 将ResultSet的结果保存到List中
            while (rst.next()) {
                Map<String, Object> map = new HashMap<>();
                for (int i = 1; i <= columnCount; i++) {
                    map.put(rsmd.getColumnLabel(i), rst.getObject(i));
                }
                //每一个map代表一条记录，把所有记录存在list中
                list.add(map);
            }
        } catch (SQLException e) {
            log.error("执行sql异常",e);
            throw new BusinessException("执行sql异常");
        }
        exeSqlResult.setResultList(list);
        exeSqlResult.setFiledList(filedList);
        exeSqlResult.setExecSql(sql);
        exeSqlResult.setExecSqlParams(params);
        exeSqlResult.setSessionId(sessionId);
        return exeSqlResult;
    }

    @Override
    public void trueAutoCommit() {
        try {
            this.conn.setAutoCommit(true);
        } catch (SQLException e) {
            log.error("关闭事物失败",e);
            throw new BusinessException("关闭事物失败");
        }
    }

    @Override
    public void falseAutoCommit() {
        try {
            this.conn.setAutoCommit(false);
        } catch (SQLException e) {
            log.error("开启事物失败",e);
            throw new BusinessException("开启事物失败");
        }
    }

    @Override
    public void commitTransactional() {
        try {
            this.conn.commit();
        } catch (SQLException e) {
            log.error("提交事物失败",e);
            throw new BusinessException("提交事物失败");
        }
    }

    @Override
    public Boolean dropDatabase(String name) {
        String updateSql = "DROP DATABASE IF EXISTS " + name;
        Object[] params = null;
        int i = this.executeUpdate(updateSql, params);
        return i == 1? true:false;
    }

    @Override
    public Boolean renameDatabase(String name, String toName) {
        String updateSql = "UPDATE pg_database" +
                "        SET datname = '"+ toName +"'" +
                "        WHERE datname = '"+ name + "'";
        Object[] params = null;
        int i = this.executeUpdate(updateSql, params);
        return i == 1? true:false;
    }

    @Override
    public Boolean dropDataSchema(String name) {
        String updateSql = "drop schema "+ name +" cascade ";
        Object[] params = null;
        int i = this.executeUpdate(updateSql, params);
        return i == 1? true:false;
    }

    @Override
    public Boolean renameDataSchema(String name, String toName) {
        String updateSql = "ALTER SCHEMA  "+ name +" RENAME TO " + toName;
        Object[] params = null;
        int i = this.executeUpdate(updateSql, params);
        return i == 1? true:false;
    }

    @Override
    public Boolean dropDataTable(String name) {
        String updateSql = "DROP TABLE " + name;
        Object[] params = null;
        int i = this.executeUpdate(updateSql, params);
        return i == 1? true:false;
    }

    @Override
    public Boolean renameDataTable(String name, String toName) {
        String updateSql = "alter table "+ name +" rename to " + toName;
        Object[] params = null;
        int i = this.executeUpdate(updateSql, params);
        return i == 1? true:false;
    }

    @Override
    public Boolean addDataTableFiled(String tableName, DatabaseTableFiled addDatabaseTableFiled) {
        String filed = addDatabaseTableFiled.getFiledName() + " "+ addDatabaseTableFiled.getFiledType() + "("+ addDatabaseTableFiled.getFiledSize() +")";
        String updateSql = "ALTER TABLE "+ tableName +" ADD "+filed;
        Object[] params = null;
        int i = this.executeUpdate(updateSql, params);
        return i == 1? true:false;
    }

    @Override
    public Boolean deleteDataTableFiled(String tableName,String tableFiled) {
        String updateSql = "alter table "+ tableName + " drop column if exists " + tableFiled;
        Object[] params = null;
        int i = this.executeUpdate(updateSql, params);
        return i == 1? true:false;
    }

    @Override
    public Boolean alertDataTableFiled(String tableName, String tableFiled, DatabaseTableFiled toDatabaseTableFiled) {
        String type =  toDatabaseTableFiled.getFiledType() + "("+ toDatabaseTableFiled.getFiledSize() +")";

        String updateNameSql = "alter table "+ tableName +" RENAME " + tableFiled + " to "+toDatabaseTableFiled.getFiledName();
        String updateSql = "alter table "+ tableName +" alter COLUMN  " + toDatabaseTableFiled.getFiledName() + " type " +type;
        Object[] params = null;
        int i = this.executeUpdate(updateNameSql, params);
        i += this.executeUpdate(updateSql, params);
        return i >0? true:false;
    }

    @Override
    public Boolean addFiledPk(String tableName, String tableFiled) {
        String pkUpdate = "ALTER TABLE \""+ tableName +"\" ADD CONSTRAINT \""+ tableFiled +"_pkey\" PRIMARY KEY (\""+ tableFiled +"\");";
        Object[] params = null;
        int i = this.executeUpdate(pkUpdate, params);
        return i >0? true:false;
    }

    @Override
    public Boolean deleteFiledPk(String tableName, String tableFiled) {
        String pkUpdate = "ALTER TABLE \""+ tableName +"\" DROP CONSTRAINT \""+ tableFiled +"_pkey\"";
        Object[] params = null;
        int i = this.executeUpdate(pkUpdate, params);
        return i >0? true:false;
    }

}
